﻿Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports Microsoft.Reporting.WebForms
Imports log4net

Partial Class Report_RP02
    Inherits System.Web.UI.Page

    Public ScriptText As String
    Dim Report As Object
    Private Shared logger As ILog = LogManager.GetLogger("Report_RP02")

    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

        'If Session("fundcenterS") = "" Then
        '    Session("fundcenterS") = "0011"
        '    Session("fundcenterE") = "0012"

        'End If

        'If Session("users") = "" Then
        '    Session("users") = "wassana"
        'End If
        'Session("year") = 2556
        'Session("version") = 2


        Getdata()


    End Sub


    Function Getdata() As Decimal


        Dim adapter As New Data_ReportTableAdapters.R02TableAdapter
        Dim sql As String
        Dim dt As New Data_Report.R02DataTable


        sql = " select"

        sql += "  dt.FundCenter_FundCenterCode,FundCenters.Description, "

        sql += "  CASE ISNULL(dt.Project_ProjectCode,'') WHEN '' THEN N'งบประมาณประจำ' ELSE (CASE LEFT(dt.Project_ProjectCode,1) WHEN 'P' THEN 'งบประมาณโครงการ' WHEN 'D' THEN 'งบประมาณสนับสนุน' END) END AS BudgetType, "
        sql += "  CASE ISNULL(dt.ActivityType_ActivityTypeCode,'')+ISNULL(dt.AssetNo,'') WHEN '' THEN N'รายจ่ายดำเนินงาน' ELSE N'รายจ่ายลงทุน' END AS AssetType, "

        sql += "  ISNULL(dt.Project_ProjectCode,'') as ProjectCode, "

        sql += "  CASE ISNULL(dt.Project_Fundcentercode,'')  WHEN '' THEN ISNULL((SELECT TOP 1 Description FROM Projects WHERE Projects.ProjectCode = dt.Project_ProjectCode),'') ELSE ISNULL(p.Description,'') END as ProjectDescription, "

        sql += "  h1.Description  as Description1, h2.Description  as Description2, (Cmmts.CmmtCode + ' ' + Cmmts.Description) as Description3, SUM(isnull(dt.Amount,'0'))  as Column1"

        sql += "  from BudgetDetails dt "

        sql += " inner join FundCenters on "

        sql += " FundCenters.FundCenterCode = dt.FundCenter_FundCenterCode And FundCenters.PlanYear = dt.FundCenter_PlanYear "

        sql += " inner join Cmmts on "

        sql += "  Cmmts.CmmtCode = dt.Cmmt_CmmtCode And Cmmts.PlanYear = dt.Cmmt_PlanYear "

        sql += " inner join CmmtHierarchies h3 on "

        sql += " h3.Cmmt_CmmtCode = dt.Cmmt_CmmtCode and h3.Cmmt_PlanYear = dt.Cmmt_PlanYear "

        sql += " and h3.CmmtGroup = '1'"

        sql += " inner join CmmtHierarchies h2 on"

        sql += "  h3.ParentCmmtHierarchy_Id = h2.Id "

        sql += " and h2.CmmtGroup = '1'"

        sql += " inner join CmmtHierarchies h1 on "

        sql += "  h2.ParentCmmtHierarchy_Id = h1.Id "

        sql += " and h1.CmmtGroup = '1'"

        sql += " left join Projects p on "

        sql += "  (p.ProjectCode = dt.Project_ProjectCode and"

        sql += "  p.PlanYear = dt.Project_PlanYear and p.FundCenterCode = ISNULL(dt.Project_FundCenterCode,'') ) or p.ProjectCode IS NULL"

        sql += " INNER JOIN FctH On dt.Fundcenter_FundcenterCode = FctH.FundCenter_FundCenterCode "

        sql += " AND dt.PlanYear = FctH.PlanYear "

        sql += " where  dt.PlanYear  = " & Session("year") & " and Version = '" & Session("version") & "'"
        If Session("FCTRmode") = "1" Then
            sql += " and  dt.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        ElseIf Session("FCTRmode") = "2" Then
            sql += " and  dt.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        End If


        If Session("CmmtMode") = "1" Then
            If Val(Session("CmmtCodeS")) <> 0 And Val(Session("CmmtCodeE")) <> 0 Then
                sql += " and  Cmmts.CmmtCode  between " & Session("CmmtCodeS") & " and " & Session("CmmtCodeE")
            End If
            If Val(Session("CmmtCodeS")) <> 0 And Val(Session("CmmtCodeE")) = 0 Then
                sql += " and  Cmmts.CmmtCode = " & Session("CmmtCodeS")
            End If
            If Val(Session("CmmtCodeS")) = 0 And Val(Session("CmmtCodeE")) <> 0 Then
                sql += " and  Cmmts.CmmtCode = " & Session("CmmtCodeE")
            End If
            If Val(Session("CmmtCodeS")) = 0 And Val(Session("CmmtCodeE")) = 0 Then
                sql += " and  Cmmts.CmmtCode  between " & "'00000000'" & " and " & "'99999999'"
            End If
        ElseIf Session("CmmtMode") = "2" Then
            If Session("CmmtParent") <> "" And Session("CmmtParent") <> "0" Then
                sql += "and cmmtcode in (select cmmt_cmmtcode from  cmmthierarchies where parentcmmthierarchy_id in (select id from cmmthierarchies "
                sql += " where id = " & Session("CmmtParent") & " or parentcmmthierarchy_id = " & Session("CmmtParent") & ")"
                sql += " or id in (select id from cmmthierarchies "
                sql += " where id = " & Session("CmmtParent") & " or parentcmmthierarchy_id = " & Session("CmmtParent") & ")"
                sql += ")"
            End If
        End If


        sql += " Group by"

        sql += " FctH.grouppath, dt.Project_Fundcentercode,dt.FundCenter_FundCenterCode,dt.FundCenter_PlanYear,FundCenters.Description,"

        sql += " dt.Project_ProjectCode,p.Description,dt.ActivityType_ActivityTypeCode,dt.AssetNo,"

        sql += "  h1.Id,h1.Description, "

        sql += "  h2.Id,h2.Description, "

        sql += " dt.Cmmt_PlanYear, dt.Cmmt_CmmtCode, Cmmts.Description,Cmmts.CmmtCode "

        sql += " HAVING SUM(dt.Amount) Is Not null "

        sql += " Order By FctH.grouppath, FundCenter_FundCenterCode, "
        sql += " CASE ISNULL(dt.Project_ProjectCode,'') WHEN '' THEN N'1' ELSE (CASE LEFT(dt.Project_ProjectCode,1) WHEN 'P' THEN N'2' WHEN 'D' THEN N'3' END) END, "
        sql += " CASE ISNULL(dt.ActivityType_ActivityTypeCode,'')+ISNULL(dt.AssetNo,'') WHEN '' THEN N'1' ELSE N'2' END, "
        sql += " dt.Project_ProjectCode, "
        sql += " dt.ActivityType_ActivityTypeCode, "
        sql += " Cmmts.CmmtCode"

        'Response.Write(sql)
        'Exit Function
        adapter.SearchNews(dt, sql)

        'Return 1
        'Exit Function      

        'add row
        Dim dt2 As New DataTable
        dt2.Columns.Add("FundCenter_FundCenterCode", Type.GetType("System.String"))
        dt2.Columns.Add("Description", Type.GetType("System.String"))
        dt2.Columns.Add("BudgetType", Type.GetType("System.String"))
        dt2.Columns.Add("AssetType", Type.GetType("System.String"))
        dt2.Columns.Add("ProjectCode", Type.GetType("System.String"))
        dt2.Columns.Add("ProjectDescription", Type.GetType("System.String"))
        dt2.Columns.Add("Description1", Type.GetType("System.String"))
        dt2.Columns.Add("Description2", Type.GetType("System.String"))
        dt2.Columns.Add("Description3", Type.GetType("System.String"))
        dt2.Columns.Add("Column1", Type.GetType("System.Decimal"))

        Dim r As DataRow

        Dim amount As Double
        For Each row As Data_Report.R02Row In dt
            amount = row.Column1

            r = dt2.NewRow()

            'if have budget = 0 not show in report
            If row.Column1 <> 0 And row.Column1 <> Nothing Then
                r("FundCenter_FundCenterCode") = row.FundCenter_FundCenterCode
                r("Description") = If(row.IsDescriptionNull, "", row.Description)
                r("BudgetType") = row.BudgetType
                r("AssetType") = row.AssetType
                r("ProjectCode") = row.ProjectCode
                r("ProjectDescription") = row.ProjectDescription
                r("Description1") = row.Description1
                r("Description2") = row.Description2
                r("Description3") = row.Description3
                r("Column1") = row.Column1
                dt2.Rows.Add(r)
            End If

        Next

        dt2.TableName = "R02DT"
        Dim das As New DataSet
        das.DataSetName = "R01DS"
        das.Tables.Add(dt2)


        ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local
        'ReportViewer1.LocalReport.ReportPath = System.Environment.CurrentDirectory & "Report\R01.rdlc"
        ReportViewer1.LocalReport.DataSources.Clear()
        ReportViewer1.LocalReport.DataSources.Add(New Microsoft.Reporting.WebForms.ReportDataSource("DataSet1", das.Tables("R02DT")))
        ReportViewer1.DocumentMapCollapsed = True

        Dim UserName As String = Session("users")
        Dim years As String = Session("year")
        Dim version As String = Session("version")
        Dim users As String = Session("users")

        Dim p1, p2, p3 As ReportParameter
        p1 = New ReportParameter("year", years)
        p2 = New ReportParameter("version", version)
        p3 = New ReportParameter("username", UserName)

        ReportViewer1.LocalReport.SetParameters(p1)
        ReportViewer1.LocalReport.SetParameters(p2)
        ReportViewer1.LocalReport.SetParameters(p3)


        ReportViewer1.LocalReport.Refresh()


        'Response.Write(dt.Rows.Count & sql)
        'Session("count") = dt.Rows.Count
        Session("count") = dt2.Rows.Count


        Return dt2.Rows.Count

    End Function


    Public Function nPageNumber() As String
        Dim str As String
        str = Me.Report.Globals!PageNumber.ToString()
        Return str
    End Function

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Session("users") Is Nothing Then
            ScriptText = "<script>wclose();</script>"
        End If
    End Sub
End Class
